namespace PMS.BLL
{
    using PMS.DBUtility;
    using PMS.Model;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class JobTitleBLL
    {
        public int Add(UserJobTitle model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into UserJobTitle(");
            builder.Append("TitleName,");
            builder.Append("TitleTypeID");
            builder.Append(") values (");
            builder.Append("@TitleName,");
            builder.Append("@TitleTypeID");
            builder.Append(") ");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@TitleName", SqlDbType.NVarChar, 50), new SqlParameter("@TitleTypeID", SqlDbType.Int, 4) };
            cmdParms[0].Value = model.TitleName;
            cmdParms[1].Value = model.TitleTypeID;
            object single = DBHelper.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public bool Delete(int TitleID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from UserJobTitle ");
            builder.Append(" where TitleID=@TitleID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@TitleID", SqlDbType.Int, 4) };
            cmdParms[0].Value = TitleID;
            return (DBHelper.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool Exists(int TitleID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(TitleID) from UserJobTitle");
            builder.Append(" where ");
            builder.Append(" TitleID = @TitleID  ");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@TitleID", SqlDbType.Int, 4) };
            cmdParms[0].Value = TitleID;
            return DBHelper.Exists(builder.ToString(), cmdParms);
        }

        public List<UserJobTitle> GetList()
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select * ");
            builder.Append(" FROM UserJobTitle ");
            DataSet set = DBHelper.Query(builder.ToString());
            List<UserJobTitle> list = new List<UserJobTitle>();
            if (set.Tables[0].Rows.Count > 0)
            {
                UserJobTitle item = null;
                foreach (DataRow row in set.Tables[0].Rows)
                {
                    item = new UserJobTitle();
                    if (row["TitleID"].ToString() != "")
                    {
                        item.TitleID = int.Parse(row["TitleID"].ToString());
                    }
                    item.TitleName = row["TitleName"].ToString();
                    item.TitleTypeID = int.Parse(row["TitleTypeID"].ToString());
                    list.Add(item);
                }
            }
            return list;
        }

        public UserJobTitle GetModel(int TitleID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select TitleID, TitleName,  TitleTypeID");
            builder.Append("  from UserJobTitle ");
            builder.Append(" where TitleID=@TitleID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@TitleID", SqlDbType.Int, 4) };
            cmdParms[0].Value = TitleID;
            UserJobTitle title = new UserJobTitle();
            DataSet set = DBHelper.Query(builder.ToString(), cmdParms);
            if (set.Tables[0].Rows.Count > 0)
            {
                if (set.Tables[0].Rows[0]["TitleID"].ToString() != "")
                {
                    title.TitleID = int.Parse(set.Tables[0].Rows[0]["TitleID"].ToString());
                }
                title.TitleName = set.Tables[0].Rows[0]["TitleName"].ToString();
                title.TitleTypeID = int.Parse(set.Tables[0].Rows[0]["TitleTypeID"].ToString());
                return title;
            }
            return null;
        }

        public bool Update(UserJobTitle model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update UserJobTitle set ");
            builder.Append(" TitleTypeID = @TitleTypeID , ");
            builder.Append(" TitleName = @TitleName  ");
            builder.Append(" where TitleID=@TitleID ");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@TitleID", SqlDbType.Int, 4), new SqlParameter("@TitleName", SqlDbType.NVarChar, 50), new SqlParameter("@TitleTypeID", SqlDbType.Int, 4) };
            cmdParms[0].Value = model.TitleID;
            cmdParms[1].Value = model.TitleName;
            cmdParms[2].Value = model.TitleTypeID;
            return (DBHelper.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }
    }
}

